Introduction to Power Query Editor in Power BI

Course: Microsoft Power BI Training

Topic: Introduction to Power Query Editor

Course Link: Microsoft Power BI Training at JustAcademy

Demo Link: Register for a Free Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Power Query Editor

Power Query Editor is a data preparation and transformation tool available in Microsoft Power BI. It allows users to connect to different data sources, inspect raw data, clean incorrect values, remove unnecessary information, change data types, combine datasets, and prepare data for analysis.

In real-world projects, data is rarely ready for reporting immediately. Data collected from Excel files, databases, websites, business applications, and other systems may contain missing values, duplicate records, inconsistent formats, incorrect data types, and unnecessary columns. Power Query Editor helps solve these problems before the data enters the Power BI data model.

Power Query records the transformations applied to a query as individual steps. When the data is refreshed, Power Query can run those steps again, subject to the source's availability and permissions.

For example, a company may receive monthly sales reports containing thousands of records. Before creating sales dashboards, an analyst can use Power Query Editor to remove blank rows, standardize product names, convert sales amounts to numeric values, and combine monthly files into one dataset.

1.1 What Is Power Query?

Power Query is Microsoft's data connectivity and data preparation technology. It provides a graphical interface for importing and transforming data and uses the Power Query M formula language to define transformations.

Power Query is used in Power BI and several other Microsoft products. The final destination of the prepared data depends on the application or service in which Power Query is being used.

1.2 What Is Power Query Editor?

Power Query Editor is the interface where users create, view, modify, and manage queries. A query describes how data is obtained from a source and how it is transformed before being loaded or used by the destination application.

Most common transformations can be performed using menus and ribbon commands without manually writing code. Power Query generates the corresponding M expressions behind the scenes.

1.3 Power Query and Power Query Editor: Difference

2. Why Is Power Query Editor Important?

Power Query Editor is important because reliable reports depend on reliable data. If the source data contains errors or inconsistent formats, calculations and visualizations may produce misleading results.

2.1 Data Cleaning

Users can remove duplicate records, filter unwanted rows, replace incorrect values, handle blanks, and standardize text.

2.2 Data Transformation

Data can be reshaped to match reporting requirements. Examples include splitting a full name into separate columns, extracting the month from a date, converting text values into numbers, and pivoting or unpivoting data.

2.3 Data Integration

Power Query can combine information from multiple sources. For example, an analyst can merge sales data with a product table or append monthly sales files into one consolidated dataset.

2.4 Repeatable Workflows

Instead of manually repeating the same cleaning process every month, an analyst can define transformation steps once and run them again during refresh. Refresh results still depend on the source structure, connectivity, credentials, and query configuration.

2.5 Improved Reporting Quality

Well-prepared data makes it easier to build accurate charts, tables, measures, and dashboards in Power BI.

3. Understanding the ETL Process

Power Query Editor is commonly associated with the ETL process: Extract, Transform, and Load.

3.1 Example of ETL

Suppose a retail company maintains sales information in an Excel file. The file contains transaction dates, product names, quantities, prices, and customer names.

1. Extract: Connect Power BI to the Excel workbook.
2. Transform: Remove empty records, convert transaction dates into the Date type, and calculate a line total if required.
3. Load: Select Close & Apply to apply the changes and load the resulting data into Power BI Desktop.
4. Analyze: Create visuals that show sales by month, product, or region.

4. How to Open Power Query Editor

Power Query Editor can be opened from Power BI Desktop after launching a report or starting a new project.

4.1 Step-by-Step Procedure

1. Open Microsoft Power BI Desktop.
2. Create a new report or open an existing PBIX file.
3. On the Home tab, select Get data to connect to a source, such as Excel, CSV, or SQL Server.
4. Select the required file, database, or data source.
5. Choose the relevant table or dataset in the Navigator window.
6. Select Transform Data to open Power Query Editor.
7. Inspect the preview and begin preparing the data.

You can also select Home > Transform data in Power BI Desktop to open the editor for an existing report.

4.2 Load Versus Transform Data

5. Main Components of Power Query Editor

Power Query Editor contains several interface areas. Understanding them helps users navigate queries and perform transformations efficiently. The exact layout may vary between editor experiences and software versions.

5.1 Ribbon

The ribbon contains tabs and commands for connecting to data, transforming columns, adding calculated columns, managing queries, and changing the editor's view.

Common tabs include Home, Transform, Add Column, View, Tools, and Help. Available tabs and commands can vary by version and context.

5.2 Queries Pane

The Queries pane, usually on the left, displays the queries available in the current Power Query session. Selecting a query displays its preview and settings.

Example query names include Sales, Customers, Products, Employees, and Orders. Descriptive names make larger projects easier to maintain.

5.3 Data Preview Pane

The central preview displays sample or previewed rows and columns from the selected query. Users can select column headers, open filter menus, inspect values, and apply transformations.

The preview is not necessarily the complete dataset. Large sources may be sampled or previewed according to the connector and editor's behavior.

5.4 Query Settings Pane

The Query Settings pane displays properties of the selected query. Two important areas are:

• Properties: Contains the query name and, where supported, its description.
• Applied Steps: Lists the transformations performed on the query in sequence.

5.5 Formula Bar

The formula bar displays the M expression for the selected step when the formula bar is enabled. It allows users to inspect or edit a single step's expression.

5.6 Advanced Editor

The Advanced Editor displays the M script for the selected query. It is useful for reviewing or editing the complete sequence of query steps.

5.7 Status Bar and Views

The status area may display information about the preview, processing status, row or column counts, and available view controls. The exact information varies by editor experience and context.

6. Understanding Queries in Power Query

A query is a set of instructions that describes how to connect to a data source and prepare its data. A query can contain source navigation, filtering, column selection, type changes, calculations, and other transformation steps.

6.1 Example of a Sales Query

Suppose a table contains these columns:

The Sales query could contain steps to connect to the source, set numeric data types, remove unnecessary columns, and create a total value column. Each step forms part of the query's transformation sequence.

6.2 Renaming a Query

1. Select the query in the Queries pane.
2. Locate the query name in Query Settings.
3. Replace the default name with a meaningful name, such as Sales_Cleaned.
4. Press Enter to confirm the name.

Use consistent naming conventions to distinguish raw data queries, transformed queries, and final reporting tables.

7. Understanding Applied Steps

Applied Steps is one of the most important features of Power Query Editor. Each transformation is recorded as a step, allowing users to review how the data changes as the query progresses.

7.1 Example of Applied Steps

A query may contain the following steps:

1. Source: Connects to the original file or database.
2. Navigation: Selects the required table or object from the source.
3. Promoted Headers: Uses the first row as column names when appropriate.
4. Changed Type: Assigns data types to the columns.
5. Filtered Rows: Keeps only records matching a condition.
6. Removed Columns: Removes unnecessary fields.
7. Added Custom: Adds a calculated column.

7.2 How to Inspect a Step

1. Select the required query.
2. Locate Applied Steps in Query Settings.
3. Select an individual step.
4. Review the data preview to understand the result at that point.
5. Use the step's context menu to rename, edit, or remove it when appropriate.

7.3 Editing and Deleting Steps

Some steps have a settings icon that allows the transformation to be reconfigured. Other steps can be renamed, deleted, or reordered through the context menu where supported.

Deleting or moving a step may affect later steps. For example, if a column is removed and a later step references that column, the query may return an error.

7.4 Why Applied Steps Matter

• They make data preparation easier to review.
• They reduce the need to repeat manual cleaning operations.
• They help identify which transformation introduced an error.
• They document the sequence of query operations.
• They make refreshable data preparation workflows possible.

8. Basic Data Cleaning Operations

Data cleaning means identifying and correcting data quality issues before analysis. Power Query Editor provides menu-based tools for many common cleaning tasks.

8.1 Remove Unnecessary Columns

Columns that are not needed for reporting can increase model size and make the dataset more difficult to understand.

1. Select the unwanted column or columns.
2. Right-click and select Remove Columns, or use the relevant ribbon command.
3. Check the preview to confirm that the required fields remain.

Example: A sales report may contain internal comments and temporary processing IDs that are not required for analysis.

8.2 Remove Unnecessary Rows

Rows can be filtered or removed when they contain irrelevant information, such as blank records, test transactions, or data outside the required reporting period.

1. Open the filter menu for the relevant column.
2. Select the values or conditions to retain.
3. Apply the filter and inspect the resulting preview.

Before removing rows, confirm that they are genuinely unnecessary. A blank value in one column does not always mean the entire row should be deleted.

8.3 Remove Duplicate Records

Duplicate records may cause totals to be overstated. To remove duplicates, select the column or combination of columns that defines uniqueness and use the Remove Duplicates command.

Important: Removing duplicates based on one column is different from removing duplicates based on every column. Choose the columns according to the business definition of a duplicate.

8.4 Replace Values

Replace Values can standardize inconsistent or incorrect entries.

For example, if a Region column contains values such as "N", "North", and "NORTH", these values may need to be standardized. Verify that the values have the same business meaning before replacing them.

8.5 Handle Null and Blank Values

Null values represent missing or unavailable data. Depending on the business context, you may filter them, replace them, preserve them, or investigate their cause.

• Replace missing categories only when an appropriate business rule exists.
• Do not automatically replace missing numeric values with zero unless zero is the correct meaning.
• Check whether blank values indicate incomplete records or legitimate missing information.

9. Understanding Data Types

A data type defines the kind of value a column contains. Correct data types are essential for sorting, calculations, filtering, relationships, and time-based analysis.

9.1 How to Change a Data Type

1. Select the column header.
2. Select the data type icon near the column name, or use the Data Type command on the ribbon.
3. Choose the appropriate type.
4. Review any conversion errors and verify the results.

9.2 Example of a Data Type Problem

Suppose a sales amount column contains values stored as text. Power BI may not treat the values as numeric values for aggregation. Changing the column to an appropriate numeric type can resolve the issue if all values are valid.

However, values such as "1,250.50" may be interpreted differently depending on locale settings. If conversion fails, use the appropriate locale when changing the type and verify the result.

10. Transforming Text and Date Columns

10.1 Text Transformations

Text transformations help standardize labels and extract useful information from strings.

• Trim: Removes leading and trailing spaces.
• Clean: Removes certain non-printable characters.
• Uppercase: Converts text to uppercase.
• Lowercase: Converts text to lowercase.
• Capitalize Each Word: Capitalizes the first character of words where appropriate.
• Split Column: Divides a column based on a delimiter or character position.
• Extract: Retrieves a portion of a text value.

Example: A column containing " Mumbai " can be trimmed to "Mumbai" so that inconsistent spaces do not affect grouping or matching.

10.2 Splitting a Column

Suppose a column named FullName contains "Amit Sharma". You can split it by a space delimiter into separate columns, such as FirstName and LastName, if the source's naming conventions support this approach.

1. Select the column.
2. Choose Split Column from the ribbon or the column's context menu.
3. Select By Delimiter.
4. Choose the delimiter, such as a space or comma.
5. Review the split result and rename the new columns.

Splitting names on spaces may not work reliably for every person because names can contain multiple parts. Consider the structure of the source data before using this transformation.

10.3 Date Transformations

Power Query can extract the year, quarter, month, day, and other date components from a valid date column.

For example, a transaction date of 2026-10-21 can be used to derive the year 2026, month 10, or day 21.

1. Ensure the source column has a valid Date or Date/Time type.
2. Select the column.
3. Open the Date options under the Transform or Add Column ribbon.
4. Select the required date component.
5. Rename the resulting column clearly.

11. Adding Custom Columns

A custom column allows users to create a new field using an expression based on existing columns. This is useful for row-level calculations and conditional logic.

11.1 Example: Calculate Total Sales

Suppose the Sales table contains Quantity and UnitPrice columns. A custom column can calculate the total for each row.

1. Select the Add Column tab.
2. Choose Custom Column.
3. Enter a name such as TotalSales.
4. Enter a formula that multiplies Quantity by UnitPrice.
5. Confirm the formula and inspect the new column.
6. Set the new column to an appropriate numeric data type.

Example M expression:

[Quantity] * [UnitPrice]

If Quantity is 3 and UnitPrice is 1200, TotalSales is 3600.

11.2 Example: Conditional Column

A conditional column creates values based on one or more rules. For example, a sales performance label could classify sales of 100000 or more as "High" and lower sales as "Standard".

Example M expression:

if [SalesAmount] >= 100000 then "High" else "Standard"

The referenced column must exist, and its data type must support the comparison. Null values may require additional conditions depending on the business rules.

12. Introduction to the Power Query M Language

Power Query M is the formula language used to define queries and transformations. The graphical interface creates M expressions automatically, but understanding basic M syntax makes it easier to troubleshoot and customize queries.

12.1 Example of M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    ChangedType = Table.TransformColumnTypes(
        Source,
        {
            {"Quantity", Int64.Type},
            {"UnitPrice", Currency.Type}
        }
    ),
    AddedTotalSales = Table.AddColumn(
        ChangedType,
        "TotalSales",
        each [Quantity] * [UnitPrice],
        Currency.Type
    )
in
    AddedTotalSales

This example assumes an Excel table named Sales is available in the current workbook and contains the expected columns.

12.2 Understanding the Code

• let: Starts a sequence of named expressions.
• Source: Defines the initial source step.
• ChangedType: Converts Quantity and UnitPrice to specified data types.
• AddedTotalSales: Creates a TotalSales column by multiplying Quantity and UnitPrice.
• in: Specifies which final expression is returned as the query result.

12.3 Formula Bar Versus Advanced Editor

13. Previewing Data and Profiling Columns

Before loading data, analysts should inspect the preview to understand the shape, quality, and consistency of the data.

13.1 Data Preview

The data preview shows rows and columns for the selected query. It allows users to verify that transformations have produced the expected results.

13.2 Column Quality

Where enabled and supported, column quality indicators help identify valid values, errors, and empty values. These indicators are useful for finding potential data quality issues.

13.3 Column Distribution

Column distribution provides information about distinct and unique values. It can help identify repeated categories or unexpected value patterns.

13.4 Column Profile

Column profiling provides more detailed statistics about a selected column. Depending on the editor settings, profiling may be based on the top 1,000 rows or the entire dataset. Check the profiling scope before drawing conclusions about large datasets.

14. Combining Data with Merge and Append

Power Query can combine data from multiple queries. Two important operations are Merge Queries and Append Queries.

14.1 Merge Queries

Merge Queries joins two tables based on one or more matching columns. It is similar in concept to a database join.

Example: A Sales table contains ProductID and Quantity, while a Products table contains ProductID and ProductName. Merging the tables using ProductID can add product details to sales records.

1. Select the primary query.
2. Choose Merge Queries.
3. Select the second query.
4. Select the matching key column or columns in both tables.
5. Choose an appropriate join kind.
6. Confirm the merge.
7. Expand the resulting nested table column and select the fields required.

Check that the join keys have compatible data types and that duplicate keys in the lookup table will not create unexpected repeated matches.

14.2 Append Queries

Append Queries stacks rows from two or more tables. It is useful when multiple tables share a compatible structure.

Example: JanuarySales, FebruarySales, and MarchSales can be appended to create a single table containing transactions from all three months.

1. Select a query.
2. Choose Append Queries or Append Queries as New.
3. Select the tables to append.
4. Confirm the operation.
5. Check the resulting columns and data types.

15. Understanding Query Dependencies and References

In larger Power BI projects, one query may depend on another. Query dependencies help users understand how data moves from source queries to transformed queries.

15.1 Reference Queries

A reference query creates a new query based on the output of an existing query. It is useful when multiple reporting tables need to reuse the same cleaned source data.

For example, a cleaned Sales query could be referenced to create separate queries for regional reporting or specialized analysis.

15.2 Duplicate Queries

Duplicating a query creates a separate copy of the query definition at the time of duplication. Subsequent changes to the original query are not automatically copied to the duplicate.

15.3 Reference Versus Duplicate

16. Close & Apply: Loading Prepared Data

After cleaning and transforming data, users generally apply their changes to make the prepared results available in Power BI Desktop.

16.1 Steps to Apply Changes

1. Review the final query previews.
2. Check column names and data types.
3. Confirm that important rows and columns have not been removed accidentally.
4. Select Close & Apply from the editor's Home ribbon or the available File menu, depending on the experience.
5. Wait while Power BI applies the query changes.
6. Return to Power BI Desktop and inspect the loaded tables in the data model.

16.2 What Happens After Applying Changes?

Power BI processes the query transformations and loads the resulting data according to the model and storage configuration. The data can then be used to create relationships, measures, calculated columns, and report visuals as appropriate.

Power Query Editor does not directly create report visuals. Its primary purpose is preparing data before analysis.

17. Power Query Editor Best Practices

• Use meaningful names: Rename queries and steps so other analysts can understand their purpose.
• Remove unnecessary fields: Keep only the columns required for reporting or downstream transformations.
• Set data types deliberately: Check dates, numeric values, identifiers, and text columns.
• Validate filters: Confirm that filters do not accidentally exclude important records.
• Handle nulls carefully: Apply business rules rather than replacing missing values automatically.
• Inspect applied steps: Review the transformation sequence when unexpected results appear.
• Check merge keys: Confirm matching columns, compatible data types, and expected key uniqueness.
• Keep transformations maintainable: Use clear names and avoid unnecessary duplicate operations.
• Understand query folding: When supported, some transformations can be translated and executed by the source system. This can improve performance, so avoid breaking folding unnecessarily on large datasets.
• Test refreshes: Check that queries still work when new data arrives or source files change.
• Protect credentials: Use appropriate data-source permissions and avoid embedding secrets in query expressions.
• Validate results: Compare row counts, totals, and key values against trusted source information.

18. Common Problems and Troubleshooting

19. Practical Example: Cleaning a Sales Dataset

In this exercise, you will prepare a small sales dataset in Power Query Editor.

19.1 Sample Dataset

19.2 Exercise Steps

1. Enter the sample dataset into Excel and format it as a table named Sales.
2. Open Power BI Desktop and select Get Data to import the Excel table.
3. Select Transform Data to open Power Query Editor.
4. Rename the query to Sales_Cleaned.
5. Check that OrderID is stored using a suitable identifier type and Quantity and UnitPrice are numeric.
6. Trim text columns if the source contains unnecessary spaces.
7. Review the rows and remove records only if they are genuinely invalid or unnecessary.
8. Add a custom column named TotalSales using the formula [Quantity] * [UnitPrice].
9. Set TotalSales to an appropriate numeric type.
10. Verify that the totals are correct and select Close & Apply.

19.3 Expected Results

Practice result: The total sales value across these four sample transactions is 121100. Use the value to check the custom column and confirm that the data was prepared correctly.

20. Real-World Applications of Power Query Editor

• Sales analytics: Clean sales transactions, combine monthly files, and standardize product categories.
• Finance: Prepare expense reports, reconcile records, and standardize currency and date fields.
• Human resources: Clean employee data, normalize department names, and combine employee lists.
• Marketing: Combine campaign data, clean customer lists, and standardize campaign labels.
• Inventory management: Combine product and stock tables and identify missing or inconsistent product information.
• Customer analysis: Clean customer records and prepare data for segmentation and reporting.

21. Interview Questions and Answers

Q1. What is Power Query Editor?

Answer: Power Query Editor is the interface in Power BI used to connect to data sources, clean and transform data, and prepare query results for loading into the data model.

Q2. How do you open Power Query Editor in Power BI Desktop?

Answer: Select Home > Transform data in Power BI Desktop. When connecting to a new source, select Transform Data to open the editor.

Q3. What is a query?

Answer: A query contains instructions for retrieving data and applying transformations to produce the required output.

Q4. What are Applied Steps?

Answer: Applied Steps records the sequence of transformations performed on a query. Users can inspect individual steps to understand how the data changes.

Q5. What is the difference between Power Query and DAX?

Answer: Power Query and its M language are mainly used for connecting to, cleaning, and transforming data before or during loading. DAX is used to define calculations such as measures and calculated columns in the Power BI data model.

Q6. What is the M language?

Answer: M is the formula language used by Power Query to define data retrieval and transformation operations.

Q7. What is the difference between Merge and Append?

Answer: Merge combines related data using matching columns, while Append stacks rows from multiple tables.

Q8. What is a custom column?

Answer: A custom column is a new column created using an expression based on existing columns or other values.

Q9. What does Close & Apply do?

Answer: It applies the query changes and closes Power Query Editor, making the prepared results available in Power BI Desktop according to the model configuration.

Q10. Does Power Query change the original source file?

Answer: In a typical Power BI import workflow, transformations create a prepared query result rather than modifying the original source file. Source modification depends on the connector and the operation; ordinary Power BI query transformations generally do not write changes back to the source.

Q11. Why are data types important?

Answer: Correct data types help Power BI perform calculations, sorting, filtering, date analysis, and relationships correctly.

Q12. What is query folding?

Answer: Query folding occurs when Power Query translates supported transformation steps into operations that the source system can execute. This can improve performance by reducing the amount of data processed locally.

Q13. What is the difference between Reference and Duplicate?

Answer: A reference query uses another query's output as its starting point, whereas a duplicate creates a separate copy of the query definition.

Q14. Can Power Query Editor remove duplicate records?

Answer: Yes. Select the appropriate column or columns and use Remove Duplicates. The selected columns determine what counts as a duplicate.

Q15. Why should an analyst inspect Applied Steps when a query fails?

Answer: A step may reference a missing column, use an incompatible data type, or apply a transformation that no longer works after the source changes. Inspecting the steps helps locate the source of the error.

22. Exercises for Practice

1. Import an Excel workbook into Power BI and open Power Query Editor.
2. Rename the query using a meaningful name.
3. Remove an unnecessary column and verify the result.
4. Change a text-based numeric column to a numeric type.
5. Filter a table to show records from one region.
6. Remove duplicates using an appropriate business key.
7. Split a text column using a delimiter.
8. Add a custom column that calculates quantity multiplied by unit price.
9. Merge two tables using a matching key.
10. Append two tables with compatible structures.
11. Inspect Applied Steps and identify the M expression for one transformation.
12. Apply the changes and verify the resulting table in Power BI Desktop.

23. Quick Revision Checklist

• Power Query Editor is used to prepare and transform data.
• Open the editor through Home > Transform data in Power BI Desktop.
• The Queries pane lists the queries available in the current editing session.
• The data preview displays the selected query's data.
• Query Settings contains query properties and Applied Steps.
• The formula bar displays the M expression for the selected step when enabled.
• The Advanced Editor displays the full M script for a query.
• Common transformations include filtering, removing columns, changing data types, splitting columns, and adding custom columns.
• Merge combines related tables using matching keys.
• Append stacks rows from multiple tables.
• Power Query uses M; DAX is used for calculations in the data model.
• Close & Apply applies query changes in Power BI Desktop.
• Always validate the transformed data before building reports.

24. Conclusion

Power Query Editor is a fundamental part of Microsoft Power BI because it helps analysts convert raw data into a structured and reliable format for reporting. By learning how to navigate its interface, manage queries, inspect Applied Steps, clean data, change data types, create custom columns, and combine datasets, users can build repeatable data preparation workflows.

For beginners, the best approach is to practise with small datasets first, check the results after every transformation, and gradually learn the M language. These skills provide a strong foundation for more advanced Power BI topics, including data modeling, DAX calculations, dashboard development, and report optimization.

25. Additional Learning Resources

• Microsoft Learn: Query Overview in Power BI Desktop
• Microsoft Learn: Common Query Tasks
• Microsoft Learn: What Is Power Query?
• Explore Microsoft Power BI Training at JustAcademy
• Register for a JustAcademy Course Demo
• Visit the JustAcademy Website